package com.xinchuang.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * TODO
 *
 * @author zkwczx
 * @version 1.0
 * @date 2021/3/18 18:04
 */
public class DBConManager {

    /**
     * 操作日志表
     */
    private static final String CREATE_LOG_TABLE = "CREATE TABLE IF NOT EXISTS system_log (id TEXT PRIMARY KEY, username TEXT, operation TEXT, time INTEGER, method TEXT, params TEXT, ip TEXT, operationTime NUMERIC DEFAULT (datetime('now','localtime')), url TEXT)";
    /**
     * 用户主表
     */
    private static final String CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS user_table (id TEXT PRIMARY KEY,username TEXT,password TEXT,nick_name TEXT, token TEXT,create_time NUMERIC DEFAULT (datetime('now','localtime')),login_time NUMERIC DEFAULT (datetime('now','localtime')),update_time NUMERIC DEFAULT (datetime('now','localtime')))";
    /**
     * 权限表
     */
    private static final String CREATE_ROLE_TABLE = "CREATE TABLE IF NOT EXISTS role_table (r_id INTEGER PRIMARY KEY, role_name TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')))";
    /**
     * 用户权限关联表
     */
    private static final String CREATE_USER_ROLE_TABLE = "CREATE TABLE IF NOT EXISTS user_role_rel (user_id TEXT, r_id INTEGER)";
    /**
     * 目标主表
     */
    private static final String CREATE_TARGET_TABLE = "CREATE TABLE IF NOT EXISTS target_data (target_id TEXT PRIMARY KEY,target_name TEXT,target_classify TEXT,country TEXT,target_type TEXT,lon DOUBLE,lat DOUBLE,height DOUBLE,icon TEXT,icon_url TEXT,sqlite_url TEXT,create_time NUMERIC DEFAULT (datetime('now','localtime')),update_time NUMERIC DEFAULT (datetime('now','localtime')),delete_sign INTEGER)";
    /**
     * 目标关联表
     */
    private static final String CREATE_TARGET_RELATION = "CREATE TABLE IF NOT EXISTS target_relation (id TEXT PRIMARY KEY,target_id TEXT,relation_id TEXT,relationskip TEXT,source TEXT,create_time NUMERIC DEFAULT (datetime('now','localtime')))";
    /**
     * 系统主表
     */
    private static final String CREATE_SYSTEM_ANALYZE_MASTER = "CREATE TABLE IF NOT EXISTS system_master (s_id TEXT PRIMARY KEY, system_name TEXT, target_type TEXT, include INTEGER, stat TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')), update_time NUMERIC DEFAULT (datetime('now','localtime')),remark TEXT,user_sign TEXT,task_sign TEXT,icon TEXT,icon_url TEXT)";
    /**
     * 系统关联表
     */
    private static final String CREATE_SYSTEM_RELATION = "CREATE TABLE IF NOT EXISTS system_relation (s_rel_id TEXT PRIMARY KEY, s_id TEXT, target_id TEXT, hit_degree TEXT)";
    /**
     * 体系主表
     */
    private static final String CREATE_SYSTEMS_ANALYZE_MASTER = "CREATE TABLE IF NOT EXISTS systems_master (sys_id TEXT PRIMARY KEY, systems_name TEXT, include_type TEXT, include INTEGER, stat TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')), update_time NUMERIC DEFAULT (datetime('now','localtime')),remark TEXT,user_sign TEXT,task_sign TEXT,icon TEXT,icon_url TEXT)";
    /**
     * 体系关联表
     */
    private static final String CREATE_SYSTEMS_RELATION = "CREATE TABLE IF NOT EXISTS systems_relation (sys_rel_id TEXT PRIMARY KEY, sys_id TEXT, target_id TEXT, target_type TEXT, hit_degree TEXT)";
    /**
     * 体系关系表
     */
    private static final String CREATE_SYSTEMS_RELATIONSHIP = "CREATE TABLE IF NOT EXISTS systems_relationship (sys_ship_id TEXT PRIMARY KEY, sys_id TEXT, target_one TEXT, target_two TEXT, point TEXT, relation_type TEXT)";
    /**
     * 共有属性表
     */
    private static final String CREATE_COMMON_ATTRIBUTE = "CREATE TABLE IF NOT EXISTS common_attribute (id TEXT PRIMARY KEY, key_name TEXT, key TEXT, key_type TEXT, show_type TEXT, required TEXT, info TEXT, data_range TEXT, verify TEXT, min TEXT, max TEXT, enumerator TEXT, row TEXT,sign INTEGER, message TEXT, sort INTEGER, create_time NUMERIC DEFAULT (datetime('now','localtime')))";
    /**
     * 目标类别树
     */
    private static final String CREATE_TARGET_TYPE = "CREATE TABLE IF NOT EXISTS target_type (type_id TEXT PRIMARY KEY, parent_id TEXT,type_name TEXT, level INTEGER, create_time NUMERIC DEFAULT (datetime('now','localtime')), sort INTEGER)";
    /**
     * 武器类别树
     */
    private static final String CREATE_WEAPON_TYPE = "CREATE TABLE IF NOT EXISTS weapon_type (type_id TEXT PRIMARY KEY, parent_id TEXT,type_name TEXT, level INTEGER, create_time NUMERIC DEFAULT (datetime('now','localtime')), sort INTEGER)";
    /**
     * 专有属性表
     */
    private static final String CREATE_SPECIAL_ATTRIBUTE = "CREATE TABLE IF NOT EXISTS special_attribute (id TEXT PRIMARY KEY, type_id TEXT,key_name TEXT, key TEXT, key_type TEXT, show_type TEXT, required TEXT, info TEXT, data_range TEXT, verify TEXT, min TEXT, max TEXT, enumerator TEXT, row TEXT, message TEXT, sort INTEGER, create_time NUMERIC DEFAULT (datetime('now','localtime')))";
    /**
     * 专有属性表
     */
    private static final String CREATE_WEAPON_ATTRIBUTE = "CREATE TABLE IF NOT EXISTS weapon_attribute (id TEXT PRIMARY KEY, type_id TEXT,key_name TEXT, key TEXT, key_type TEXT, show_type TEXT, required TEXT, info TEXT, data_range TEXT, verify TEXT, min TEXT, max TEXT, enumerator TEXT, row TEXT, message TEXT, sort INTEGER, create_time NUMERIC DEFAULT (datetime('now','localtime')))";

    /**
     * 统计属性关联表
     */
    private static final String CREATE_INFO_ELEMENT = "CREATE TABLE IF NOT EXISTS info_element (id TEXT PRIMARY KEY, s_id TEXT, sys_id TEXT, key TEXT, value TEXT, sign INTEGER)";

    /**
     * 目标统计属性表
     */
    private static final String CREATE_TARGET_INFO_ELEMENT = "CREATE TABLE IF NOT EXISTS target_info (id TEXT PRIMARY KEY, target_id TEXT, key TEXT, value DOUBLE, name TEXT, sign INTEGER)";

    /**
     * 系统其他信息
     */
    private static final String CREATE_SYSTEM_ELSE_DATA_SQL = "CREATE TABLE IF NOT EXISTS system_else_table (id TEXT, s_id TEXT, `key` TEXT, key_name TEXT, `key_type` TEXT, key_value TEXT, remark TEXT, person TEXT, `source` TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')), PRIMARY KEY (id))";

    /**
     * 体系其他信息
     */
    private static final String CREATE_SYSTEMS_ELSE_DATA_SQL = "CREATE TABLE IF NOT EXISTS systems_else_table (id TEXT, sys_id TEXT, `key` TEXT, key_name TEXT, `key_type` TEXT, key_value TEXT, remark TEXT, person TEXT, `source` TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')), PRIMARY KEY (id))";

    /**
     * 目标知识维护
     */
    private static final String CREATE_TARGET_KNOWLEDGE_SQL = "CREATE TABLE IF NOT EXISTS target_knowledge_table (id TEXT, rel_id TEXT, user_id TEXT, kind TEXT, name TEXT, type TEXT, index_data TEXT, remark TEXT, weapon TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')), PRIMARY KEY (id))";

    /**
     * 系统知识维护
     */
    private static final String CREATE_SYSTEM_KNOWLEDGE_SQL = "CREATE TABLE IF NOT EXISTS system_knowledge_table (id TEXT, rel_id TEXT, user_id TEXT, kind TEXT, name TEXT, type TEXT, index_data TEXT, remark TEXT, weapon TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')), PRIMARY KEY (id))";

    /**
     * 体系知识维护
     */
    private static final String CREATE_SYSTEMS_KNOWLEDGE_SQL = "CREATE TABLE IF NOT EXISTS systems_knowledge_table (id TEXT, rel_id TEXT, user_id TEXT, kind TEXT, name TEXT, type TEXT, index_data TEXT, remark TEXT, weapon TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')), PRIMARY KEY (id))";

    /**
     * 知识维护关联表
     */
    private static final String CREATE_KNOW_RELATION_SQL = "CREATE TABLE IF NOT EXISTS know_relation_table (id TEXT, rel_id TEXT, index_one TEXT, index_two TEXT, index_three TEXT, effect TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')), PRIMARY KEY (id))";

    /**
     * 价值分析关联表
     */
    private static final  String CREATE_VALUE_ANALYZE_SQL = "CREATE TABLE IF NOT EXISTS value_table (id TEXT, rel_id TEXT, s_id TEXT, target_id TEXT, sub_id TEXT ,know_id TEXT, score TEXT, weight TEXT, goal TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')), PRIMARY KEY (id))";

    /**
     * 武器关系表
     */
    private static final String CREATE_WEAPON_SQL = "CREATE TABLE IF NOT EXISTS weapon_table (id INTEGER primary key AUTOINCREMENT, weapon_name TEXT, remark TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')))";

    /**
     * 系统体系类型表
     */
    private static final String CREATE_SYSTEM_TYPE_SQL = "CREATE TABLE IF NOT EXISTS system_type (id TEXT primary key, type_name TEXT, type_id TEXT, status INTEGER, del INTEGER, create_time NUMERIC DEFAULT (datetime('now','localtime')))";

    /**
     * 知识维护头部表
     */
    private static final String CREATE_KNOW_HEARD_SQL = "CREATE TABLE IF NOT EXISTS know_heard (id TEXT primary key, remark TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')))";

    /**
     * 系统/体系经纬度表
     */
    private static final String CREATE_SYSTEMS_POSITION = "CREATE TABLE IF NOT EXISTS system_position (id TEXT primary key, positions TEXT, viewDistances TEXT, height TEXT, ready TEXT, remark TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')))";

    /**
     * 图片绑定表
     */
    private static final String SIGN_SQL = "CREATE TABLE IF NOT EXISTS sign_icon (id TEXT primary key, icon TEXT)";

    /**
     * 类型半径表
     */
    private static final String TYPE_SQL = "CREATE TABLE IF NOT EXISTS type_else (id TEXT primary key, radius INTEGER)";

    /**
     * 打击程度表
     */
    private static final String BLOW_SQL = "CREATE TABLE IF NOT EXISTS blow_levels (id INTEGER primary key AUTOINCREMENT, target_id TEXT, sys_id TEXT, message TEXT)";

    /**
     * 系统配置表
     */
    private static final String SYSTEM_CONFIG = "CREATE TABLE IF NOT EXISTS system_config (id TEXT primary key, name TEXT, remark TEXT, color TEXT, type INTEGER, create_time NUMERIC DEFAULT (datetime('now','localtime')))";

    /**
     *  目标其他消息表
     */
    private static final String TARGET_ELSE = "CREATE TABLE IF NOT EXISTS target_else (id TEXT primary key, target_id TEXT, sub_id TEXT, name TEXT, address TEXT, status TEXT, remark TEXT, message TEXT, type INTEGER, create_time NUMERIC DEFAULT (datetime('now','localtime')))";

    /**
     *  武器库信息表
     */
    private static final String CREATE_WEAPON_MESSAGE_SQL = "CREATE TABLE IF NOT EXISTS weapon_message (id LONG, `key` TEXT, relation_id TEXT, key_name TEXT, `key_type` TEXT, key_value TEXT, remark TEXT, person TEXT, `source` TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')), PRIMARY KEY (id))";

    /**
     *  武器库信息表
     */
    private static final String CREATE_WEAPON_TARGET_SQL = "CREATE TABLE IF NOT EXISTS weapon_target_relation (id TEXT, target_id TEXT, type_id TEXT, weapon_id TEXT, position TEXT, icon TEXT, remark TEXT, create_time NUMERIC DEFAULT (datetime('now','localtime')), PRIMARY KEY (id))";

    /**
     *  图片关联信息表
     */
    private static final String CREATE_ICON_TABLE_SQL = "CREATE TABLE IF NOT EXISTS icon_table (id TEXT, icon_url TEXT, type INTEGER, remark TEXT, PRIMARY KEY (id))";

    /**
     * 想定分析关联表
     */
    private static final String CREATE_IDEA_TABLE_SQL = "CREATE TABLE IF NOT EXISTS idea_table (id TEXT, system_id TEXT, target_id TEXT, stage INTEGER, remark TEXT, PRIMARY KEY (id))";

    public static void createLogDB(String sqliteUrl) throws SQLException, ClassNotFoundException {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:" + sqliteUrl + "/log-sqlite.db");
            ps = connection.prepareStatement(CREATE_LOG_TABLE);
            ps.execute();
        } finally {
            if (null != ps) {
                ps.close();
            }
            if (null != connection) {
                connection.close();
            }
        }
    }

    public static void createDB(String sqliteUrl) throws ClassNotFoundException, SQLException {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:" + sqliteUrl + "/target-sqlite.db");
            ps = connection.prepareStatement(CREATE_USER_TABLE);
            ps.execute();
            ps = connection.prepareStatement(CREATE_ROLE_TABLE);
            ps.execute();
            ps = connection.prepareStatement(CREATE_USER_ROLE_TABLE);
            ps.execute();
            ps = connection.prepareStatement(CREATE_TARGET_TABLE);
            ps.execute();
            ps = connection.prepareStatement(CREATE_TARGET_RELATION);
            ps.execute();
            ps = connection.prepareStatement(CREATE_SYSTEM_ANALYZE_MASTER);
            ps.execute();
            ps = connection.prepareStatement(CREATE_SYSTEM_RELATION);
            ps.execute();
            ps = connection.prepareStatement(CREATE_SYSTEMS_ANALYZE_MASTER);
            ps.execute();
            ps = connection.prepareStatement(CREATE_SYSTEMS_RELATION);
            ps.execute();
            ps = connection.prepareStatement(CREATE_SYSTEMS_RELATIONSHIP);
            ps.execute();
            ps = connection.prepareStatement(CREATE_COMMON_ATTRIBUTE);
            ps.execute();
            ps = connection.prepareStatement(CREATE_TARGET_TYPE);
            ps.execute();
            ps = connection.prepareStatement(CREATE_WEAPON_TYPE);
            ps.execute();
            ps = connection.prepareStatement(CREATE_SPECIAL_ATTRIBUTE);
            ps.execute();
            ps = connection.prepareStatement(CREATE_WEAPON_ATTRIBUTE);
            ps.execute();
            ps = connection.prepareStatement(CREATE_INFO_ELEMENT);
            ps.execute();
            ps = connection.prepareStatement(CREATE_TARGET_INFO_ELEMENT);
            ps.execute();
            ps = connection.prepareStatement(CREATE_SYSTEM_ELSE_DATA_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_SYSTEMS_ELSE_DATA_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_TARGET_KNOWLEDGE_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_SYSTEM_KNOWLEDGE_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_SYSTEMS_KNOWLEDGE_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_KNOW_RELATION_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_VALUE_ANALYZE_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_WEAPON_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_SYSTEM_TYPE_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_KNOW_HEARD_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_SYSTEMS_POSITION);
            ps.execute();
            ps = connection.prepareStatement(SIGN_SQL);
            ps.execute();
            ps = connection.prepareStatement(TYPE_SQL);
            ps.execute();
            ps = connection.prepareStatement(BLOW_SQL);
            ps.execute();
            ps = connection.prepareStatement(SYSTEM_CONFIG);
            ps.execute();
            ps = connection.prepareStatement(TARGET_ELSE);
            ps.execute();
            ps = connection.prepareStatement(CREATE_WEAPON_MESSAGE_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_WEAPON_TARGET_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_ICON_TABLE_SQL);
            ps.execute();
            ps = connection.prepareStatement(CREATE_IDEA_TABLE_SQL);
            ps.execute();
        } finally {
            if (null != ps) {
                ps.close();
            }
            if (null != connection) {
                connection.close();
            }
        }
    }

}
